#INCLUDE "Protheus.ch"

/*
Programa..........: AVIBETO.PRW
Arquivo Utilizado.: SD2->Itens da Nota Fiscal
Objetivo..........: Listar os produtos de vendas por Cliente, Totalizando produto
Programador.......: Wanderson/Marcelo Santana
Data Criacao......: 19/03/2009
Atualizacao por...:	Everton Alves
Data Atualiz......: 27/11/2014
Motivo da Atuliz..: 1. Buscar dados da tabela SD2
                  : 2. Recriado utilizando a classe TReport
Menu              : Relatorio Aviacao -> Analise Mensal -> Roberto
*/

USER FUNCTION AVIBETO()

oReport := ReportDef()
oReport:PrintDialog()

RETURN NIL

//-----------------------------------------------------------------
STATIC FUNCTION ReportDef()
LOCAL aResumo := {}
LOCAL cPerg := "BETO"
LOCAL oReport := NIL
LOCAL oNota := NIL
LOCAL oItens := NIL
LOCAL oResumo := NIL

Pergunte(cPerg, .F.)
oReport := TReport():New("AVIBETO", "Produtos Vendidos por Cliente/Loja", cPerg, {|oReport| ReportPrint(oReport)}, OEMTOANSI("Este programa tem como objetivo gerar um relatorio de Clientes x Produtos."))
oReport:SetLandscape()
oReport:lEndReport := .F.

oNota := TRSection():New(oReport, "Nota", {"SD2", "SA1"}, /*aOrder*/, /*lLoadCells*/, /*lLoadOrder*/, /*uTotalText*/, /*lTotalInLine*/, /*lHeaderPage*/, /*lHeaderBreak*/, /*lPageBreak*/, /*lLineBreak*/, /*nLeftMargin*/, /*lLineStyle*/)
oNota:SetHeaderPage(.F.)
oNota:SetHeaderSection(.T.)
TRCell():New(oNota, "D2_EMISSAO", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "D2_DOC", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "D2_SERIE", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "D2_CLIENTE", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "D2_LOJA", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "A1_NOME", "SA1", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "A1_END", "SA1", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "A1_MUN", "SA1", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "A1_EST", "SA1", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oNota, "D2_PEDIDO", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)

oItens := TRSection():New(oNota, "Itens", {"SD2", "SB1"})
oItens:SetHeaderSection(.T.)
oItens:SetTotalInLine(.F.)
TRCell():New(oItens, "nItem", "   ", "Item", "@!", 03,/*lPixel*/, /*{|| code-block de impressao }*/)
TRCell():New(oItens, "D2_COD", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oItens, "B1_DESC", "SB1", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oItens, "nCaixas", "   ", "Caixas", "@E 99,999.99", 08, /*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oItens, "nFormas", "   ", "Formas", "@E 99,999.99", 08, /*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oItens, "nPesLiq", "   ", "Peso Liq.", "@E 999,999.999", 10,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oItens, "nPesBru", "   ", "Peso Brut.", "@E 999,999.999", 10,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oItens, "D2_TOTAL", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oItens, "D2_VALBRUT", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)

TRFunction():New(oItens:Cell("D2_COD"), NIL, "COUNT", /*oBreak*/,/*cTitle*/,/*cPicture*/,/*uFormula*/,.T./*lEndSection*/,.F./*lEndReport*/,.F./*lEndPage*/)
TRFunction():New(oItens:Cell("nCaixas"), NIL, "SUM", /*oBreak*/,/*cTitle*/,/*cPicture*/,/*uFormula*/,.T./*lEndSection*/,.F./*lEndReport*/,.F./*lEndPage*/)
TRFunction():New(oItens:Cell("nFormas"), NIL, "SUM", /*oBreak*/,/*cTitle*/,/*cPicture*/,/*uFormula*/,.T./*lEndSection*/,.F./*lEndReport*/,.F./*lEndPage*/)
TRFunction():New(oItens:Cell("nPesLiq"), NIL, "SUM", /*oBreak*/,/*cTitle*/,/*cPicture*/,/*uFormula*/,.T./*lEndSection*/,.F./*lEndReport*/,.F./*lEndPage*/)
TRFunction():New(oItens:Cell("nPesBru"), NIL, "SUM", /*oBreak*/,/*cTitle*/,/*cPicture*/,/*uFormula*/,.T./*lEndSection*/,.F./*lEndReport*/,.F./*lEndPage*/)
TRFunction():New(oItens:Cell("D2_TOTAL"), NIL, "SUM", /*oBreak*/,/*cTitle*/,/*cPicture*/,/*uFormula*/,.T./*lEndSection*/,.F./*lEndReport*/,.F./*lEndPage*/)
TRFunction():New(oItens:Cell("D2_VALBRUT"), NIL, "SUM", /*oBreak*/,/*cTitle*/,/*cPicture*/,/*uFormula*/,.T./*lEndSection*/,.F./*lEndReport*/,.F./*lEndPage*/)

oResumo := TRSection():New(oReport, "Resumo", {"SB1"})
oResumo:SetTotalInLine(.F.)
TRCell():New(oResumo, "D2_COD", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oResumo, "B1_DESC", "SB1", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oResumo, "nTotCxs", "  ", "Caixas", "@E 999,999.99", 09, /*lPixel*/, /*{|| code-block de impressao }*/)
TRCell():New(oResumo, "nTotFrm", "  ", "Formas", "@E 999,999.99", 09, /*lPixel*/, /*{|| code-block de impressao }*/)
TRCell():New(oResumo, "nTotPesLiq", "  ", "Peso Liq.", "@E 999,999.999", 10, /*lPixel*/, /*{|| code-block de impressao }*/)
TRCell():New(oResumo, "nTotPesBru", "  ", "Peso Bruto", "@E 999,999.999", 10, /*lPixel*/, /*{|| code-block de impressao }*/)
TRCell():New(oResumo, "D2_TOTAL", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)
TRCell():New(oResumo, "D2_VALBRUT", "SD2", /*Titulo*/,/*Picture*/,/*Tamanho*/,/*lPixel*/,/*{|| code-block de impressao }*/)

TRFunction():New(oResumo:Cell("D2_COD"), NIL, "COUNT", /*oBreak*/,/*cTitle*/,/*cPicture*/,/*uFormula*/,.T./*lEndSection*/,.F./*lEndReport*/,.F./*lEndPage*/)
TRFunction():New(oResumo:Cell("nTotCxs"), NIL, "SUM", /*oBreak*/,/*cTitle*/,/*cPicture*/,/*uFormula*/,.T./*lEndSection*/,.F./*lEndReport*/,.F./*lEndPage*/)
TRFunction():New(oResumo:Cell("nTotFrm"), NIL, "SUM", /*oBreak*/,/*cTitle*/,/*cPicture*/,/*uFormula*/,.T./*lEndSection*/,.F./*lEndReport*/,.F./*lEndPage*/)
TRFunction():New(oResumo:Cell("nTotPesLiq"), NIL, "SUM", /*oBreak*/,/*cTitle*/,/*cPicture*/,/*uFormula*/,.T./*lEndSection*/,.F./*lEndReport*/,.F./*lEndPage*/)
TRFunction():New(oResumo:Cell("nTotPesBru"), NIL, "SUM", /*oBreak*/,/*cTitle*/,/*cPicture*/,/*uFormula*/,.T./*lEndSection*/,.F./*lEndReport*/,.F./*lEndPage*/)
TRFunction():New(oResumo:Cell("D2_TOTAL"), NIL, "SUM", /*oBreak*/,/*cTitle*/,/*cPicture*/,/*uFormula*/,.T./*lEndSection*/,.F./*lEndReport*/,.F./*lEndPage*/)
TRFunction():New(oResumo:Cell("D2_VALBRUT"), NIL, "SUM", /*oBreak*/,/*cTitle*/,/*cPicture*/,/*uFormula*/,.T./*lEndSection*/,.F./*lEndReport*/,.F./*lEndPage*/)

RETURN (oReport)

//-----------------------------------------------------------------
STATIC FUNCTION ReportPrint(oReport)
LOCAL aResumo := {}
LOCAL cAlias := GetNextAlias()
LOCAL cProdDe := mv_par01
LOCAL cProdAte := mv_par02
LOCAL cCliDe := mv_par05
LOCAL cCliAte := mv_par06
LOCAL cLojaDe := mv_par07
LOCAL cLojaAte := mv_par08
LOCAL cMarca := AllTrim(mv_par09)
LOCAL cProd := ""
LOCAL cDesc := ""
LOCAL cFiltro := "%%"
LOCAL cPeriodo := "%%"
LOCAL dEmisDe := mv_par03
LOCAL dEmisAte := mv_par04
LOCAL lMarca := !EMPTY(mv_par09)
LOCAL lAnalitic := mv_par11 == 1
LOCAL lFirst := .F.
LOCAL nCaixas := 0
LOCAL nFormas := 0
LOCAL nPesLiq := 0
LOCAL nPesBru := 0
LOCAL nItem := 0
LOCAL nRecCount := 0
LOCAL nPos := 0
LOCAL nTotCxs := 0
LOCAL nTotFrm := 0
LOCAL nTotPesLiq := 0
LOCAL nTotPesBru := 0
LOCAL nTotVlr := 0
LOCAL nTotVlrBru := 0
LOCAL nI := 0
LOCAL nQtdRes := 0

IF lMarca
	cFiltro := "%AND C5_MARCA = '" + cMarca + "'%"
ENDIF

cPeriodo :=	"%" + IF(MV_PAR10 == 1, "D2_EMISSAO", "C5_DTENTRG") + " BETWEEN '" + DTOS(dEmisDe) + "' AND '" + DTOS(dEmisAte) + "' %"

IF lAnalitic
	oReport:Section(1):BeginQuery()	
		BEGINSQL ALIAS cAlias
		SELECT D2_EMISSAO,
		       D2_DOC,
		       D2_SERIE,
		       D2_CLIENTE,
		       D2_LOJA,
		       A1_NOME,
		       A1_END,
		       A1_MUN,
		       A1_EST,
		       D2_COD,
		       SUM(D2_QUANT) D2_QUANT,
		       SUM(D2_TOTAL) D2_TOTAL,
		       SUM(D2_VALBRUT) D2_VALBRUT,
		       D2_PEDIDO,
		       C5_MARCA
		  FROM %Table:SD2% SD2
		 INNER JOIN %Table:SC5% SC5 ON C5_NUM = D2_PEDIDO
	                      AND C5_FILIAL = %xFilial:SC5%
	                      AND SC5.%NotDel%
		 INNER JOIN %Table:SA1% SA1 ON A1_COD = D2_CLIENTE
	                      AND A1_LOJA = D2_LOJA
	                      AND A1_FILIAL = %xFilial:SA1%
	                      AND SA1.%NotDel%
		 WHERE %Exp:cPeriodo%
		   AND D2_COD BETWEEN %Exp:cProdDe% AND %Exp:cProdAte%
		   AND D2_CLIENTE BETWEEN %Exp:cCliDe% AND %Exp:cCliAte%
		   AND D2_LOJA BETWEEN %Exp:cLojaDe% AND %Exp:cLojaAte%
		   AND D2_FILIAL = %xFilial:SD2%
		   AND SD2.%NotDel%
		   %Exp:cFiltro%
		 GROUP BY D2_EMISSAO,
		          D2_DOC,
		          D2_SERIE,
		          D2_CLIENTE,
		          D2_LOJA,
		          A1_NOME,
		          A1_END,
		          A1_MUN,
		          A1_EST,
		          D2_COD,
		          D2_PEDIDO,
		          C5_MARCA
		 ORDER BY D2_DOC, D2_SERIE, D2_CLIENTE, D2_LOJA
		ENDSQL 
	oReport:Section(1):EndQuery()
ELSE
	oReport:Section(2):BeginQuery()	
		BEGINSQL ALIAS cAlias
		SELECT D2_COD,
		       B1_DESC,
		       SUM(D2_QUANT) D2_QUANT,
		       SUM(D2_TOTAL) D2_TOTAL,
		       SUM(D2_VALBRUT) D2_VALBRUT,
		       SUM(C6_UNSVEN) C6_UNSVEN,
		       SUM(C6_QTDPCT) C6_QTDPCT,
		       SUM(C6_QTDVEN) C6_QTDVEN,
		       SUM(C6_QTDBRU) C6_QTDBRU
		  FROM (SELECT D2_COD,
		               SUM(D2_QUANT) D2_QUANT,
		               SUM(D2_TOTAL) D2_TOTAL,
		               SUM(D2_VALBRUT) D2_VALBRUT,
		               D2_PEDIDO
		          FROM %Table:SD2% SD2
		         INNER JOIN %Table:SC5% SC5 ON C5_NUM = D2_PEDIDO
		                              AND C5_FILIAL = %xFilial:SC5%
		                              AND SC5.%NotDel%
		         WHERE %Exp:cPeriodo%
		           AND D2_COD BETWEEN %Exp:cProdDe% AND %Exp:cProdAte%
		           AND D2_CLIENTE BETWEEN %Exp:cCliDe% AND %Exp:cCliAte%
		           AND D2_LOJA BETWEEN %Exp:cLojaDe% AND %Exp:cLojaAte%
		           AND D2_FILIAL = %xFilial:SD2%
		           AND SD2.%NotDel%
		           %Exp:cFiltro%
		         GROUP BY D2_COD, D2_PEDIDO)
		 INNER JOIN %Table:SC6% SC6 ON C6_NUM = D2_PEDIDO
		                      AND C6_PRODUTO = D2_COD
		                      AND C6_FILIAL = %xFilial:SC6%
		                      AND SC6.%NotDel%
		 INNER JOIN %Table:SB1% SB1 ON B1_COD = D2_COD
		                      AND B1_FILIAL = %xFilial:SB1%
		                      AND SB1.%NotDel%
		 GROUP BY D2_COD, B1_DESC
		 ORDER BY D2_COD
		ENDSQL 
	oReport:Section(2):EndQuery()
ENDIF

dbSelectArea(cAlias)
COUNT TO nRecCount
(cAlias)->(dbGoTop())
oReport:SetMeter(nRecCount)

dbSelectArea("SB1")
SB1->(dbSetOrder(1))

IF lAnalitic
	oReport:Section(1):Section(1):SetParentQuery()
	oReport:Section(1):Init()
	lFirst := .T.
	
	WHILE !(cAlias)->(EOF()) .And. !oReport:Cancel()
		oReport:IncMeter()
		IF lFirst
			lFirst := .F.
		ELSE
			oReport:Section(1):PrintHeader()
		ENDIF
		oReport:Section(1):PrintLine()
		oReport:Section(1):Section(1):Init()
		cChave := (cAlias)->(D2_DOC + D2_SERIE + D2_CLIENTE + D2_LOJA)
		nItem := 0
		
		WHILE !(cAlias)->(EOF()) .And. !oReport:Cancel() .And. (cAlias)->(D2_DOC + D2_SERIE + D2_CLIENTE + D2_LOJA) == cChave
			nCaixas := 0
			nFormas := 0
			nPeso := 0
			SB1->(dbSeek(xFilial("SB1") + (cAlias)->D2_COD))
			
			IF RTRIM(SB1->B1_GRUPO) $ "3" .And. !(RTRIM((cAlias)->D2_COD) $ "310000/315500")
				nItem++
				nCaixas := (cAlias)->C6_UNSVEN
				nFormas := (cAlias)->C6_QTDPCT
				nPesLiq := (cAlias)->C6_QTDVEN
				nPesBru := (cAlias)->C6_QTDBRU
			ELSE
				IF EMPTY(SB1->B1_CONV) .OR. EMPTY(SB1->B1_TIPCONV)
					nItem++
					nCaixas := (cAlias)->D2_QUANT
					nFormas := 0
					nPesLiq := 0
					nPesBru := 0
				ELSE
					nItem++
					nCaixas := IF(SB1->B1_TIPCONV $ "D", (cAlias)->D2_QUANT / SB1->B1_CONV, (cAlias)->D2_QUANT * SB1->B1_CONV)
					nFormas := 0
					nPesLiq := nCaixas * SB1->B1_PESO
					nPesBru := nCaixas * SB1->B1_PESBRU
				ENDIF
			ENDIF
			
			oReport:Section(1):Section(1):Cell("nItem"):SetBlock({|| STRZERO(nItem, 3)})
			oReport:Section(1):Section(1):Cell("nCaixas"):SetBlock({|| nCaixas})
			oReport:Section(1):Section(1):Cell("nFormas"):SetBlock({|| nFormas})
			oReport:Section(1):Section(1):Cell("nPesLiq"):SetBlock({|| nPesLiq})
			oReport:Section(1):Section(1):Cell("nPesBru"):SetBlock({|| nPesBru})
			oReport:Section(1):Section(1):PrintLine()
			(cAlias)->(dbSkip())
		END WHILE
		
		oReport:Section(1):Section(1):Finish()
		oReport:SkipLine()
		oReport:SkipLine()
		
	END WHILE
	
	oReport:Section(1):Finish()
ELSE
	oReport:Section(2):Init()
	
	WHILE (cAlias)->(!EOF()) .AND.  !oReport:Cancel()
		oReport:IncMeter()
		nTotCxs := 0
		nTotFrm := 0
		nTotPesLiq := 0
		nTotPesBru := 0
		SB1->(dbSeek(xFilial("SB1") + (cAlias)->D2_COD))
			
		IF RTRIM(SB1->B1_GRUPO) $ "3" .And. !(RTRIM((cAlias)->D2_COD) $ "310000/315500")
			nTotCxs := (cAlias)->C6_UNSVEN
			nTotFrm := (cAlias)->C6_QTDPCT
			nTotPesLiq := (cAlias)->C6_QTDVEN
			nTotPesBru := (cAlias)->C6_QTDBRU
		ELSE
			IF EMPTY(SB1->B1_CONV) .OR. EMPTY(SB1->B1_TIPCONV)
				nTotCxs := (cAlias)->D2_QUANT
				nTotFrm := 0
				nTotPesLiq := 0
				nTotPesBru := 0
			ELSE
				nTotCxs := IF(SB1->B1_TIPCONV $ "D", (cAlias)->D2_QUANT / SB1->B1_CONV, (cAlias)->D2_QUANT * SB1->B1_CONV)
				nTotFrm := 0
				nTotPesLiq := nTotCxs * SB1->B1_PESO
				nTotPesBru := nTotCxs * SB1->B1_PESBRU
			ENDIF
		ENDIF
		
		oReport:Section(2):Cell("nTotCxs"):SetBlock({|| nTotCxs})
		oReport:Section(2):Cell("nTotFrm"):SetBlock({|| nTotFrm})
		oReport:Section(2):Cell("nTotPesLiq"):SetBlock({|| nTotPesLiq})
		oReport:Section(2):Cell("nTotPesBru"):SetBlock({|| nTotPesBru})
		oReport:Section(2):PrintLine()
		(cAlias)->(dbSkip())
	END WHILE
	oReport:Section(2):Finish()

ENDIF

(cAlias)->(dbCloseArea())

RETURN NIL

//-----------------------------------------------------------------